﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Xml;

namespace TransformExcelToMySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var configFileName = args[0];

                var doc = new XmlDocument();
                doc.Load(configFileName);

                var m = GetXmlNamespaceManager(doc);

                //перебор по задачам
                foreach (var task in doc.SelectNodes("//my:task", m).Cast<XmlNode>())
                {
                    var filePath = task.Attributes["my:workbook"].Value;//название файла xlsx
                    var workSheet = task.Attributes["my:worksheet"].Value;//название листа
                    var conStrAnalysis = GetMySqlConnection(doc, task.Attributes["my:connection"].Value);
                    var tableName = task.Attributes["my:table"].Value;
                    var verName = task.Attributes["my:version_name"].Value;
                    var verValue = task.Attributes["my:version_value"].Value;
                    var ver = string.IsNullOrEmpty(verName) ? string.Empty : string.Format(@" WHERE {0} = {1}", verName, verValue);
                    var cmdTxtAnalysis = string.Format(@"SELECT * FROM {0}{1}", tableName, ver);

                    using (var adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(cmdTxtAnalysis, conStrAnalysis))
                    {
                        //команды для адаптера
                        var builder = new MySql.Data.MySqlClient.MySqlCommandBuilder(adapter);
                        adapter.InsertCommand = builder.GetInsertCommand();
                        adapter.UpdateCommand = builder.GetUpdateCommand();
                        adapter.DeleteCommand = builder.GetDeleteCommand();
                        builder = null;

                        //таблица для синхронизации
                        var dtAnalysis = new DataTable();
                        Console.WriteLine("{0}: DataSource: {1} Database: {2} Table: {3} Fill.", DateTime.Now, adapter.SelectCommand.Connection.DataSource, adapter.SelectCommand.Connection.Database, tableName);
                        adapter.Fill(dtAnalysis);
                        Console.WriteLine("{0}: Success.", DateTime.Now);

                        //очистить таблицу
                        foreach (var row in dtAnalysis.AsEnumerable().ToArray())
                        {
                            row.Delete();
                        }

                        using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
                        {
                            using (var reader = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream))
                            {
                                reader.IsFirstRowAsColumnNames = true;

                                if (string.IsNullOrEmpty(workSheet))
                                {
                                    workSheet = reader.AsDataSet().Tables[0].TableName;
                                }

                                //заполнить строками из оперативной БД
                                Console.WriteLine("{0}: Workbook: {1} Worksheet: {2} Read.", DateTime.Now, Path.GetFileName(filePath), workSheet);

                                //TransformDataTable(reader.AsDataSet().Tables[workSheet], dtAnalysis, task);

                                var columns = task.SelectNodes("my:columns/my:column", m).Cast<XmlNode>();
                                var sectionName = task.SelectSingleNode("my:sectional/@my:section", m).Value;
                                var sectionValue = task.SelectSingleNode("my:sectional/@my:section_value", m).Value;
                                var skipEmpty = bool.Parse(task.SelectSingleNode("my:sectional/@my:skip_empty", m).Value);

                                foreach (var row in reader.AsDataSet().Tables[workSheet].AsEnumerable())
                                {
                                    var sectional = task.SelectSingleNode("my:sectional", m);

                                    foreach (var section in sectional.SelectNodes("my:column", m).Cast<XmlNode>())
                                    {
                                        double val = 0;
                                        double.TryParse(row[section.Attributes["my:source_name"].Value].ToString(), out val);

                                        //если не пропускать или значение не пустое
                                        if (!skipEmpty || val != 0)
                                        {

                                            var newRow = dtAnalysis.NewRow();

                                            foreach (var col in columns)
                                            {
                                                var colSource = col.Attributes["my:source_name"].Value;
                                                newRow[col.InnerText] = row[string.IsNullOrEmpty(colSource) ? col.InnerText : colSource];
                                            }

                                            newRow[sectionName] = section.InnerText;
                                            newRow[sectionValue] = row[section.Attributes["my:source_name"].Value];

                                            if (!string.IsNullOrEmpty(verName))
                                            {
                                                newRow[verName] = verValue;
                                            }

                                            dtAnalysis.Rows.Add(newRow);
                                        }
                                    }
                                }

                                Console.WriteLine("{0}: Success.", DateTime.Now);
                            }
                        }

                        Console.WriteLine("{0}: DataSource: {1} Database: {2} Table: {3} Update.", DateTime.Now, adapter.SelectCommand.Connection.DataSource, adapter.SelectCommand.Connection.Database, tableName);
                        //сохранить в аналитической БД
                        adapter.Update(dtAnalysis);
                        Console.WriteLine("{0}: Success.", DateTime.Now);

                        dtAnalysis = null;
                    }
                }
            }
            catch (Exception ex)
            {
                //TODO не прерывать
                Console.WriteLine("{0}: Error:", DateTime.Now);
                Console.WriteLine(ex.Message);
            }
            finally
            {
                //TODO добавить счётчик ошибок
                Console.WriteLine("{0}: Press any key...", DateTime.Now);
                Console.ReadKey();
            }
        }

        static XmlNamespaceManager GetXmlNamespaceManager(XmlDocument doc)
        {
            var m = new XmlNamespaceManager(doc.Schemas.NameTable);
            m.AddNamespace(doc.DocumentElement.Prefix, doc.DocumentElement.NamespaceURI);
            return m;
        }

        static string GetMySqlConnection(XmlDocument doc, string connectionName)
        {
            var m = GetXmlNamespaceManager(doc);
            var xmlConnection = doc.SelectSingleNode(string.Format("//my:connection[text()='{0}']", connectionName), m);

            var builder = new MySql.Data.MySqlClient.MySqlConnectionStringBuilder()
            {
                Server = xmlConnection.Attributes["my:server"].Value,
                Database = xmlConnection.Attributes["my:database"].Value,
                UserID = xmlConnection.Attributes["my:user_id"].Value,
                Password = xmlConnection.Attributes["my:password"].Value
            };

            return builder.ConnectionString;
        }
    }
}